package com.example.biji.dao;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.biji.model.Note;

import java.util.ArrayList;
import java.util.List;

public class NoteDao {
    private static final String DATABASE_NAME = "notes.db";
    private static final int DATABASE_VERSION = 1;

    // 表名和列名
    public static final String TABLE_NAME = "notes";
    public static final String COLUMN_ID = "_id";
    public static final String COLUMN_TITLE = "title";
    public static final String COLUMN_CONTENT = "content";
    public static final String COLUMN_CREATE_TIME = "create_time";
    public static final String COLUMN_UPDATE_TIME = "update_time";
    public static final String COLUMN_USER_ID = "user_id";

    // SQL 创建表语句
    private static final String CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + " (" +
                    COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
                    COLUMN_TITLE + " TEXT NOT NULL, " +
                    COLUMN_CONTENT + " TEXT, " +
                    COLUMN_CREATE_TIME + " TEXT NOT NULL, " +
                    COLUMN_UPDATE_TIME + " TEXT NOT NULL, " +
                    COLUMN_USER_ID + " INTEGER NOT NULL);";

    private SQLiteDatabase db;

    public NoteDao(Context context) {
        DatabaseHelper helper = new DatabaseHelper(context);
        db = helper.getWritableDatabase();
    }

    // 插入笔记
    public long insertNote(Note note) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, note.getTitle());
        values.put(COLUMN_CONTENT, note.getContent());
        values.put(COLUMN_CREATE_TIME, note.getCreateTime());
        values.put(COLUMN_UPDATE_TIME, note.getUpdateTime());
        values.put(COLUMN_USER_ID, note.getUserId());

        return db.insert(TABLE_NAME, null, values);
    }

    // 更新笔记
    public int updateNote(Note note) {
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, note.getTitle());
        values.put(COLUMN_CONTENT, note.getContent());
        values.put(COLUMN_UPDATE_TIME, note.getUpdateTime());

        String whereClause = COLUMN_ID + " = ?";
        String[] whereArgs = { String.valueOf(note.getId()) };

        return db.update(TABLE_NAME, values, whereClause, whereArgs);
    }

    // 删除笔记
    public int deleteNote(int noteId) {
        String whereClause = COLUMN_ID + " = ?";
        String[] whereArgs = { String.valueOf(noteId) };

        return db.delete(TABLE_NAME, whereClause, whereArgs);
    }

    // 查询所有笔记
    public List<Note> queryAllNotes(int userId) {
        List<Note> noteList = new ArrayList<>();
        String selection = COLUMN_USER_ID + " = ?";
        String[] selectionArgs = { String.valueOf(userId) };

        Cursor cursor = db.query(
                TABLE_NAME,
                null,
                selection,
                selectionArgs,
                null,
                null,
                COLUMN_UPDATE_TIME + " DESC"
        );

        while (cursor.moveToNext()) {
            Note note = new Note();
            note.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
            note.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
            note.setContent(cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT)));
            note.setCreateTime(cursor.getString(cursor.getColumnIndex(COLUMN_CREATE_TIME)));
            note.setUpdateTime(cursor.getString(cursor.getColumnIndex(COLUMN_UPDATE_TIME)));
            note.setUserId(cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)));
            noteList.add(note);
        }
        cursor.close();
        return noteList;
    }

    // 根据ID查询笔记
    public Note queryNoteById(int noteId) {
        Note note = null;
        String selection = COLUMN_ID + " = ?";
        String[] selectionArgs = { String.valueOf(noteId) };

        Cursor cursor = db.query(
                TABLE_NAME,
                null,
                selection,
                selectionArgs,
                null,
                null,
                null
        );

        if (cursor.moveToFirst()) {
            note = new Note();
            note.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
            note.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
            note.setContent(cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT)));
            note.setCreateTime(cursor.getString(cursor.getColumnIndex(COLUMN_CREATE_TIME)));
            note.setUpdateTime(cursor.getString(cursor.getColumnIndex(COLUMN_UPDATE_TIME)));
            note.setUserId(cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)));
        }
        cursor.close();
        return note;
    }

    // 搜索笔记
    public List<Note> searchNotes(int userId, String keyword) {
        List<Note> resultList = new ArrayList<>();
        String selection = COLUMN_USER_ID + " = ? AND (" +
                COLUMN_TITLE + " LIKE ? OR " +
                COLUMN_CONTENT + " LIKE ?)";
        String[] selectionArgs = {
                String.valueOf(userId),
                "%" + keyword + "%",
                "%" + keyword + "%"
        };

        Cursor cursor = db.query(
                TABLE_NAME,
                null,
                selection,
                selectionArgs,
                null,
                null,
                COLUMN_UPDATE_TIME + " DESC"
        );

        while (cursor.moveToNext()) {
            Note note = new Note();
            note.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ID)));
            note.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
            note.setContent(cursor.getString(cursor.getColumnIndex(COLUMN_CONTENT)));
            note.setCreateTime(cursor.getString(cursor.getColumnIndex(COLUMN_CREATE_TIME)));
            note.setUpdateTime(cursor.getString(cursor.getColumnIndex(COLUMN_UPDATE_TIME)));
            note.setUserId(cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)));
            resultList.add(note);
        }
        cursor.close();
        return resultList;
    }

    // 数据库帮助类
    private static class DatabaseHelper extends SQLiteOpenHelper {
        public DatabaseHelper(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_TABLE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("DROP TABLE IF EXISTS " + TABLE_NAME);
            onCreate(db);
        }
    }
}